﻿Imports System.Net
Imports System.Net.Sockets
Imports System.Text
Imports System.Threading
Imports System.Net.NetworkInformation
Imports System.IO
Imports System.Data.SqlClient
Imports Excel = Microsoft.Office.Interop.Excel

Public Class frm_host

    Public WithEvents myChat As New TCPChat
    Private myAdapterName, myPhysicalAddress, myGateway, myDNS, strHostName As String
    Private addr() As IPAddress
    Private pool As List(Of message2Send)
    Private isOnline(10) As Boolean
    Private ping(10) As Boolean
    Private isCheckingOnline As Boolean ' = true: asked and waiting for response, = false: already receive response

    'for excel file
    Dim xlApp As Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    Dim models As New BindingSource
    Dim thisMonth, thisYear, DaysInMonth As Integer
    Dim nSheets As Integer
    Dim selectedSheet As Integer

    Dim ExcelFileName As String
    Dim ReadFromExcel As Boolean = False
    Dim sched(5, 31) As String
    Dim sname(5) As String
    Dim ptime(5, 31) As String
    Dim WorkTimeRemain(5, 31) As Integer

    'Dim fileExcelLoaded As Boolean = False

    Private Sub Form1_FormClosing(sender As Object, e As FormClosingEventArgs) Handles Me.FormClosing
        If myChat.isConnected Then myChat.disconnect()
        If Not xlApp Is Nothing Then
            CloseExcel()
        End If
    End Sub

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        'first check connection
        If Not My.Computer.Network.IsAvailable Then
            MessageBox.Show("No connection. Please check and try again!")
            Me.Close()
        End If

        '//Read HostSetting.ini
        Dim fR As StreamReader = New StreamReader(SETTING_FILE)
        Dim i As Integer
        Dim st As String

        nShops = fR.ReadLine().Replace(" ", "").Replace("nShops=", "")
        For i = 0 To nShops - 1
            st = fR.ReadLine
            Shops(i) = st.Split("|")(0)
            ShopsIP(i) = st.Split("|")(1)
            ShopsPort(i) = st.Split("|")(2)
        Next

        If nShops > 0 Then
            lbl_ip1.Text = Shops(0) & "'s IP"
            tbx_remoteIP1.Text = ShopsIP(0)
            tbx_remotePort1.Text = ShopsPort(0)
        End If
        If nShops > 1 Then
            lbl_ip2.Text = Shops(1) & "'s IP"
            tbx_remoteIP2.Text = ShopsIP(1)
            tbx_remotePort2.Text = ShopsPort(1)
        End If
        If nShops > 2 Then
            lbl_ip3.Text = Shops(2) & "'s IP"
            tbx_remoteIP3.Text = ShopsIP(2)
            tbx_remotePort3.Text = ShopsPort(2)
        End If
        If nShops > 3 Then
            lbl_ip4.Text = Shops(3) & "'s IP"
            tbx_remoteIP4.Text = ShopsIP(3)
            tbx_remotePort4.Text = ShopsPort(3)
        End If
        If nShops > 4 Then
            lbl_ip5.Text = Shops(4) & "'s IP"
            tbx_remoteIP5.Text = ShopsIP(4)
            tbx_remotePort5.Text = ShopsPort(4)
        End If
        ConnectionString = ConnectionString.Replace("{SQLserver}", _
                                                    fR.ReadLine.Replace(" ", "").Replace("SQLserver=", ""))

        nRetries = fR.ReadLine().Replace(" ", "").Replace("nRetries=", "")
        RetryEverySec = fR.ReadLine().Replace(" ", "").Replace("RetryEverySec=", "")
        CheckConnEveryMin = fR.ReadLine().Replace(" ", "").Replace("CheckConnEveryMin=", "")
        DebugMode = fR.ReadLine().Replace(" ", "").Replace("DebugMode=", "")
        ShowHiddenSheets = (fR.ReadLine().Replace(" ", "").Replace("ShowHiddenSheets=", "").Trim.Equals("1"))

        fR.Close()

        gb_debug.Visible = (DebugMode = 1)
        btn_saveDB.Visible = (DebugMode = 1)

        '//init today target
        LoadTarget()

        '//init schedule tab
        init_schedule()

        '//init stat tab
        init_stat()

        '//load working hour
        LoadWorkingHour()

        ''//init isOnline
        For i = 0 To nShops - 1
            isOnline(i) = False
            ping(i) = 0
        Next

        '// Getting Ip address of local machine.
        '// First get the host name of local machine.
        strHostName = Dns.GetHostName()
        Dim ipEntry As IPHostEntry = Dns.GetHostEntry(strHostName)
        addr = ipEntry.AddressList
        For i = 0 To addr.Length - 1

            If addr(i).AddressFamily = AddressFamily.InterNetwork Then
                StatusLabel_adapter.Text = "host " & strHostName & _
                                           String.Format(" IP: {0}", addr(i).ToString)
                Exit For
            End If
        Next

        tbx_remoteIP1.Text = ShopsIP(0)
        tbx_remotePort1.Text = ShopsPort(0)
        tbx_hostIP.Text = addr(i).ToString
        tbx_hostPort.Text = "5000"
        'tbx_remoteIP.Text = addr(i).ToString

        pool = New List(Of message2Send)

        '//load time
        Timer_clock.Interval = 10 * 1000
        Timer_clock.Enabled = True
        Timer_clock.Start()

        lbl_hour.Text = Today & "   " & Format(Now, "HH:mm")
        'lbl_hour.Left = (Me.Width - lbl_hour.Width) / 2

        btn_Connect.Visible = (DebugMode = 1)
        If DebugMode = 0 Then
            btn_Connect_Click(btn_Connect, e)
        End If

        'debug production.txt
        'ReadProdFile("BDY", "127.0.0.1")
    End Sub

    Private Sub LoadWorkingHour()
        'load time from db
        Using connection As New SqlConnection(ConnectionString)
            connection.Open()
            Dim sql = "SELECT * FROM WorkHour"

            Dim cmd As New SqlCommand(sql, connection)
            cmd.CommandType = CommandType.Text

            Dim reader As SqlDataReader = cmd.ExecuteReader()
            If (reader.Read()) Then
                DateTimePicker1.Value = DateTime.ParseExact(reader.GetValue(1), "HH:mm", Nothing)
                DateTimePicker2.Value = DateTime.ParseExact(reader.GetValue(2), "HH:mm", Nothing)
                DateTimePicker3.Value = DateTime.ParseExact(reader.GetValue(3), "HH:mm", Nothing)
                DateTimePicker4.Value = DateTime.ParseExact(reader.GetValue(4), "HH:mm", Nothing)
                DateTimePicker5.Value = DateTime.ParseExact(reader.GetValue(5), "HH:mm", Nothing)
                DateTimePicker6.Value = DateTime.ParseExact(reader.GetValue(6), "HH:mm", Nothing)
                DateTimePicker7.Value = DateTime.ParseExact(reader.GetValue(7), "HH:mm", Nothing)
                DateTimePicker8.Value = DateTime.ParseExact(reader.GetValue(8), "HH:mm", Nothing)
                DateTimePicker9.Value = DateTime.ParseExact(reader.GetValue(9), "HH:mm", Nothing)
                DateTimePicker10.Value = DateTime.ParseExact(reader.GetValue(10), "HH:mm", Nothing)
                DateTimePicker11.Value = DateTime.ParseExact(reader.GetValue(11), "HH:mm", Nothing)
            End If
            reader.Close()
            cmd.Dispose()
        End Using

        DateTimePicker1.Enabled = False
        DateTimePicker2.Enabled = False
        DateTimePicker3.Enabled = False
        DateTimePicker4.Enabled = False
        DateTimePicker5.Enabled = False
        DateTimePicker6.Enabled = False
        DateTimePicker7.Enabled = False
        DateTimePicker8.Enabled = False
        DateTimePicker9.Enabled = False
        DateTimePicker10.Enabled = False
        DateTimePicker11.Enabled = False
        btn_cancel.Enabled = False
        btn_update.Text = "Edit"
    End Sub

    Private Sub LoadTarget()
        Using connection As New SqlConnection(ConnectionString)
            connection.Open()

            Dim sql = "SELECT * FROM schedule WHERE DateId='" & Today.ToString("yyyy-MM-dd") & "'"

            Dim cmd As New SqlCommand(sql, connection)
            cmd.CommandType = CommandType.Text

            Dim shopCode As String
            Dim plan As String
            Dim models() As String
            Dim target, i As Integer

            Dim reader As SqlDataReader = cmd.ExecuteReader()
            While (reader.Read())
                target = 0

                shopCode = reader.GetString(1)
                plan = reader.GetString(2)
                models = plan.Split("|")

                For i = 0 To models.Length - 1
                    target += models(i).Split(":")(1)
                Next

                Select Case shopCode
                    Case "BDY" : lbl_bdy_target.Text = target
                    Case "CHA" : lbl_cha_target.Text = target
                    Case "PNT" : lbl_pnt_target.Text = target
                    Case "FNG" : lbl_fin_target.Text = target
                    Case "TRI" : lbl_tri_target.Text = target
                End Select
            End While
            reader.Close()
            cmd.Dispose()
        End Using
    End Sub

    Private Sub btn_Connect_Click(sender As Object, e As EventArgs) Handles btn_Connect.Click
        myChat.connect(tbx_hostIP.Text, CInt(tbx_hostPort.Text))
        If myChat.isConnected Then
            CurrentTask = TASK_ASK_ONL
            isCheckingOnline = False
            Timer_online.Interval = CheckConnEveryMin * 60 * 1000 / 2
            Timer_online.Enabled = True
            Timer_online.Start()
            Timer_online_Tick(Timer_online, e)

            '//start the pooling
            Timer_sendMsg.Interval = RetryEverySec * 1000
            Timer_sendMsg.Enabled = True
            Timer_sendMsg.Start()
        End If
    End Sub

    Private Sub btn_Disconnect_Click(sender As Object, e As EventArgs) Handles btn_Disconnect.Click
        myChat.disconnect()
    End Sub

    Private Sub btn_clear_Click(sender As Object, e As EventArgs) Handles btn_clear.Click
        lbout.Items.Clear()
    End Sub

    Private Sub connection(ByVal status As Boolean) Handles myChat.connection
        btn_Connect.Enabled = Not status
        btn_Disconnect.Enabled = status
        tbin.Enabled = status

        If status Then
            StatusLabel_adapter.Image = My.Resources.ledCornerGreen
            StatusLabel_receive.Image = My.Resources.ledCornerOrange
        Else
            StatusLabel_adapter.Image = My.Resources.ledCornerGray
            StatusLabel_receive.Image = My.Resources.ledCornerGray
            StatusLabel_send.Image = My.Resources.ledCornerGray
        End If
    End Sub

    Private Sub myChat_Datareceived(txt As String) Handles myChat.Datareceived
        lbout.Items.Add(Now & " >>In >> " & txt)

        Dim i As Integer = 0
        Dim idx As Integer = 0
        Dim shopCode As String = txt.Substring(0, 3)
        If (shopCode <> "BDY") AndAlso (shopCode <> "CHA") AndAlso _
            (shopCode <> "FNG") AndAlso (shopCode <> "PNT") AndAlso (shopCode <> "TRI") Then
            Exit Sub
        End If

        While (shopCode <> Shops(idx)) AndAlso (idx < nShops)
            idx += 1
        End While

        If txt.Contains("PROD") Then
            'ShopCode|PROD|Plan|Actual
            Select Case shopCode
                Case "BDY" '#1
                    If txt.Split("|")(3) <> lbl_bdy_actual.Text Then
                        ReadProdFile(shopCode, ShopsIP(0))
                    End If
                    pic_bdy.Image = My.Resources.green
                    lbl_bdy_plan.Text = txt.Split("|")(2)
                    lbl_bdy_actual.Text = txt.Split("|")(3)
                    lbl_bdy_diff.Text = CInt(lbl_bdy_actual.Text) - CInt(lbl_bdy_plan.Text)
                    If (CInt(lbl_bdy_diff.Text) < 0) Then
                        pnl_bdy_diff.BackColor = Color.Red
                        pnl_bdy_num.BackColor = Color.Red
                    Else
                        pnl_bdy_diff.BackColor = Color.Silver
                        pnl_bdy_num.BackColor = Color.Silver
                    End If

                Case "CHA" '#4
                    If txt.Split("|")(3) <> lbl_cha_actual.Text Then
                        ReadProdFile(shopCode, ShopsIP(3))
                    End If
                    pic_cha.Image = My.Resources.green
                    lbl_cha_plan.Text = txt.Split("|")(2)
                    lbl_cha_actual.Text = txt.Split("|")(3)
                    lbl_cha_diff.Text = CInt(lbl_cha_actual.Text) - CInt(lbl_cha_plan.Text)
                    If (CInt(lbl_cha_diff.Text) < 0) Then
                        pnl_cha_diff.BackColor = Color.Red
                        pnl_cha_num.BackColor = Color.Red
                    Else
                        pnl_cha_diff.BackColor = Color.Silver
                        pnl_cha_num.BackColor = Color.Silver
                    End If

                Case "PNT" '#2
                    If txt.Split("|")(3) <> lbl_pnt_actual.Text Then
                        ReadProdFile(shopCode, ShopsIP(1))
                    End If
                    pic_pnt.Image = My.Resources.green
                    lbl_pnt_plan.Text = txt.Split("|")(2)
                    lbl_pnt_actual.Text = txt.Split("|")(3)
                    lbl_pnt_diff.Text = CInt(lbl_pnt_actual.Text) - CInt(lbl_pnt_plan.Text)
                    If (CInt(lbl_pnt_diff.Text) < 0) Then
                        pnl_pnt_diff.BackColor = Color.Red
                        pnl_pnt_num.BackColor = Color.Red
                    Else
                        pnl_pnt_diff.BackColor = Color.Silver
                        pnl_pnt_num.BackColor = Color.Silver
                    End If

                Case "FNG" '#5
                    If txt.Split("|")(3) <> lbl_fin_actual.Text Then
                        ReadProdFile(shopCode, ShopsIP(4))
                    End If
                    pic_fin.Image = My.Resources.green
                    lbl_fin_plan.Text = txt.Split("|")(2)
                    lbl_fin_actual.Text = txt.Split("|")(3)
                    lbl_fin_diff.Text = CInt(lbl_fin_actual.Text) - CInt(lbl_fin_plan.Text)
                    If (CInt(lbl_fin_diff.Text) < 0) Then
                        pnl_fin_diff.BackColor = Color.Red
                        pnl_fin_num.BackColor = Color.Red
                    Else
                        pnl_fin_diff.BackColor = Color.Silver
                        pnl_fin_num.BackColor = Color.Silver
                    End If

                Case "TRI" '#3
                    If txt.Split("|")(3) <> lbl_tri_actual.Text Then
                        ReadProdFile(shopCode, ShopsIP(2))
                    End If
                    pic_trim.Image = My.Resources.green
                    lbl_tri_plan.Text = txt.Split("|")(2)
                    lbl_tri_actual.Text = txt.Split("|")(3)
                    lbl_tri_diff.Text = CInt(lbl_tri_actual.Text) - CInt(lbl_tri_plan.Text)
                    If (CInt(lbl_tri_diff.Text) < 0) Then
                        pnl_tri_diff.BackColor = Color.Red
                        pnl_tri_num.BackColor = Color.Red
                    Else
                        pnl_tri_diff.BackColor = Color.Silver
                        pnl_tri_num.BackColor = Color.Silver
                    End If
            End Select

            For i = 0 To nShops - 1
                If shopCode = Shops(i) Then
                    isOnline(i) = True
                    ping(i) = False 'already receive signal from this shop
                    Exit For
                End If
            Next

            'send the ack
            If idx < nShops Then
                myChat.SendData("OK", ShopsIP(idx), ShopsPort(idx))
                txtOutSend(ShopsIP(idx), "OK")
            End If


        ElseIf txt.Contains("WORK") Then
            'client reply ack for Working Schedule
            For i = pool.Count - 1 To 0 Step -1
                If (shopCode = pool(i).code) AndAlso (pool(i).msg.StartsWith("WORK")) Then
                    pool.RemoveAt(i)
                End If
            Next

        ElseIf txt.Contains("ONLN") Then
            'client reply ack for ONL status
            Select Case shopCode
                Case "BDY"
                    pic_bdy.Image = My.Resources.green

                Case "CHA"
                    pic_cha.Image = My.Resources.green

                Case "PNT"
                    pic_pnt.Image = My.Resources.green

                Case "FNG"
                    pic_fin.Image = My.Resources.green

                Case "TRI"
                    pic_trim.Image = My.Resources.green
            End Select

        ElseIf txt.Contains("SCHE") Then
            'client reply for receiving schedule
            For i = pool.Count - 1 To 0 Step -1
                If (shopCode = pool(i).code) AndAlso (pool(i).msg.StartsWith("SCHE")) Then
                    pool.RemoveAt(i)
                End If
            Next

        ElseIf txt.Contains("ASKS") Then
            'client reply with the schedule

        End If

        Dim StillHaveThings2Send As Boolean = False

        For i = 0 To pool.Count - 1
            If pool(i).nRetries < nRetries Then
                StillHaveThings2Send = True
            End If
        Next

        ''if nothing else in the pool, then resume the checkOnline
        'If Not StillHaveThings2Send Then
        '    Timer_online.Enabled = True
        '    Timer_online.Start()
        'End If

    End Sub

    Private Sub rdata(ByVal receiveStatus As Boolean) Handles myChat.recOK
        Try
            If receiveStatus Then
                StatusLabel_receive.Image = My.Resources.ledCornerGreen
                'StatusLabel_adapter.Text = "local " & myChat.Local.ToString & _
                '                           " remote" & myChat.Remote.ToString

            Else
                StatusLabel_receive.Image = My.Resources.ledCornerRed
            End If
        Catch ex As Exception
            'do nothing
        End Try
    End Sub

    Private Sub sendata(ByVal sendStatus As Boolean) Handles myChat.sendOK
        Try
            If sendStatus Then
                StatusLabel_send.Image = My.Resources.ledCornerGreen
            Else
                StatusLabel_send.Image = My.Resources.ledCornerRed
            End If
        Catch ex As Exception
            'do nothing
        End Try
    End Sub

    Private Sub tbinKD(ByVal sender As Object, _
                       ByVal e As KeyEventArgs) Handles tbin.KeyDown
        If e.KeyCode = Keys.Enter Then
            With CType(sender, TextBox)
                If .Text.Length > 0 Then
                    StatusLabel_send.Image = My.Resources.ledCornerGray
                    myChat.SendData(.Text, tbx_remoteIP1.Text, CInt(tbx_remotePort1.Text))
                    txtOutSend(tbx_remoteIP1.Text, .Text)
                    .Text = String.Empty
                End If
            End With
        End If
    End Sub

    Private Sub txtOutSend(ByVal ip As String, ByVal txt As String)
        lbout.Items.Add(Now & " <<Out(" & ip & ")<< " & txt)
        lbout.TopIndex = lbout.Items.Count - 1
    End Sub

    Private Sub Timer_Clock_Tick(sender As Object, e As EventArgs) Handles Timer_clock.Tick
        lbl_hour.Text = Today & "   " & Format(Now, "HH:mm")
        'lbl_hour.Left = (Me.Width - lbl_hour.Width) / 2
    End Sub

    Private Sub btn_working_Click(sender As Object, e As EventArgs) Handles btn_working.Click
        NeedToUpdateWorkHour = False
        frm_workHour.ShowDialog()
        If NeedToUpdateWorkHour Then
            For i = 0 To nShops - 1
                'If isOnline(i) Then
                pool.Add(New message2Send(WorkingHourString, Shops(i), ShopsIP(i), ShopsPort(i), i))
                'UpdateWorkingHour(WorkingHourString, ShopsIP(i), ShopsPort(i))
                'End If
            Next
            Timer_sendMsg_Tick(Timer_sendMsg, e)
        End If
    End Sub

    Private Sub btn_duration_Click(sender As Object, e As EventArgs) Handles btn_duration.Click
        frm_Duration.ShowDialog()
    End Sub

    Private Sub btn_sched_Click(sender As Object, e As EventArgs) Handles btn_sched.Click
        frm_schedule.ShowDialog()
    End Sub

    Private Sub btn_stat_Click(sender As Object, e As EventArgs)

    End Sub


    '============================
    '=====COMMUNICATION PART=====
    '============================
    Private Sub Timer_sendMsg_Tick(sender As Object, e As EventArgs) Handles Timer_sendMsg.Tick
        Dim i As Integer = 0
        'Dim msg As String
        'Select Case CurrentTask
        '    Case TASK_WORK
        '    Case TASK_SCHE
        '    Case TASK_ASK_SCHE
        '    Case TASK_ASK_ONL
        '        msg = "ONLN"
        '        myChat.SendData(msg, ShopsIP(i), ShopsPort(i))
        '        txtOutSend(msg)
        'End Select

        'check all the pool to see any msg need to send???
        For i = 0 To pool.Count - 1
            If (pool(i).nRetries < nRetries) AndAlso (isOnline(pool(i).shopOrder)) Then
                ''if anything need to send then stop the online checking function
                ''no, don't stop it, just it it run to keep the data updated
                'Timer_online.Enabled = False
                'Timer_online.Stop()

                pool(i).nRetries += 1
                myChat.SendData(pool(i).msg, pool(i).toIP, pool(i).toPort)
                txtOutSend(pool(i).toIP, pool(i).msg)
                Thread.Sleep(500)
            End If
        Next

        lbl_pool.Text = pool.Count
    End Sub

    'Public Sub UpdateWorkingHour(content As String, toShopIP As String, toShopPort As Integer)
    '    CurrentTask = TASK_WORK
    '    'Timer2.Interval = nRetries
    '    'Timer2.Enabled = True
    '    'Timer2.Start()
    '    'Timer2_Tick(Timer2, New EventArgs)
    '    myChat.SendData(content, toShopIP, toShopPort)
    'End Sub

    Private Sub Timer_online_Tick(sender As Object, e As EventArgs) Handles Timer_online.Tick
        Dim i As Integer = 0
        Dim msg As String

        If isCheckingOnline Then 'asked, after 15sec, check again if received responsed?
            For i = 0 To nShops - 1
                If ping(i) = True Then
                    Select Case Shops(i)
                        Case "BDY" : pic_bdy.Image = My.Resources.red
                        Case "CHA" : pic_cha.Image = My.Resources.red
                        Case "PNT" : pic_pnt.Image = My.Resources.red
                        Case "FNG" : pic_fin.Image = My.Resources.red
                        Case "TRI" : pic_trim.Image = My.Resources.red
                    End Select
                    isOnline(i) = False
                End If
            Next

            Timer_online.Interval = CheckConnEveryMin * 60000 / 2
            isCheckingOnline = False
        Else
            For i = 0 To nShops - 1
                ping(i) = True
                If My.Computer.Network.Ping(ShopsIP(i)) Then
                    msg = "PROD" 'ask for PROD instead of ONLN, if client respons then online
                    myChat.SendData(msg, ShopsIP(i), ShopsPort(i))
                    txtOutSend(ShopsIP(i), msg)
                    Thread.Sleep(500)
                End If
            Next

            isCheckingOnline = True
            Timer_online.Interval = RetryEverySec * 1000
        End If
        
    End Sub

    Private Sub btn_update_Click(sender As Object, e As EventArgs) Handles btn_update.Click
        If btn_update.Text = "Edit" Then
            'enable control
            'disable controls
            DateTimePicker1.Enabled = True
            DateTimePicker2.Enabled = True
            DateTimePicker3.Enabled = True
            DateTimePicker4.Enabled = True
            DateTimePicker5.Enabled = True
            DateTimePicker6.Enabled = True
            DateTimePicker7.Enabled = True
            DateTimePicker8.Enabled = True
            DateTimePicker9.Enabled = True
            DateTimePicker10.Enabled = True
            DateTimePicker11.Enabled = True
            btn_cancel.Enabled = True
            btn_update.Text = "Save"

        Else
            'save to db
            Using connection As New SqlConnection(ConnectionString)
                connection.Open()
                Dim sql = String.Format("UPDATE WorkHour SET MorStart='{0}'," & _
                                        "MorBreakStart = '{1}'," & _
                                        "MorBreakEnd = '{2}'," & _
                                        "LunchStart = '{3}'," & _
                                        "LunchEnd = '{4}'," & _
                                        "NoonStart = '{5}'," & _
                                        "NoonBreakStart = '{6}'," & _
                                        "NoonBreakEnd = '{7}'," & _
                                        "NoonEnd = '{8}'," & _
                                        "OTStart = '{9}'," & _
                                        "OTEnd = '{10}'", _
                                        DateTimePicker1.Value.ToString("HH:mm"), _
                                        DateTimePicker2.Value.ToString("HH:mm"), _
                                        DateTimePicker3.Value.ToString("HH:mm"), _
                                        DateTimePicker4.Value.ToString("HH:mm"), _
                                        DateTimePicker5.Value.ToString("HH:mm"), _
                                        DateTimePicker6.Value.ToString("HH:mm"), _
                                        DateTimePicker7.Value.ToString("HH:mm"), _
                                        DateTimePicker8.Value.ToString("HH:mm"), _
                                        DateTimePicker9.Value.ToString("HH:mm"), _
                                        DateTimePicker10.Value.ToString("HH:mm"), _
                                        DateTimePicker11.Value.ToString("HH:mm"))


                Dim cmd As New SqlCommand(sql, connection)
                cmd.CommandType = CommandType.Text

                Try
                    cmd.ExecuteNonQuery()
                Catch ex As Exception
                    MessageBox.Show(ex.Message, "Error when update Working Hour")
                End Try
                cmd.Dispose()

            End Using

            'disable controls
            DateTimePicker1.Enabled = False
            DateTimePicker2.Enabled = False
            DateTimePicker3.Enabled = False
            DateTimePicker4.Enabled = False
            DateTimePicker5.Enabled = False
            DateTimePicker6.Enabled = False
            DateTimePicker7.Enabled = False
            DateTimePicker8.Enabled = False
            DateTimePicker9.Enabled = False
            DateTimePicker10.Enabled = False
            DateTimePicker11.Enabled = False
            btn_cancel.Enabled = False
            btn_update.Text = "Edit"

            'download to tool
            WorkingHourString = "WORK"
            WorkingHourString &= "|" & DateTimePicker1.Value.ToString("HH:mm")
            WorkingHourString &= "|" & DateTimePicker2.Value.ToString("HH:mm")
            WorkingHourString &= "|" & DateTimePicker3.Value.ToString("HH:mm")
            WorkingHourString &= "|" & DateTimePicker4.Value.ToString("HH:mm")
            WorkingHourString &= "|" & DateTimePicker5.Value.ToString("HH:mm")
            WorkingHourString &= "|" & DateTimePicker6.Value.ToString("HH:mm")
            WorkingHourString &= "|" & DateTimePicker7.Value.ToString("HH:mm")
            WorkingHourString &= "|" & DateTimePicker8.Value.ToString("HH:mm")
            WorkingHourString &= "|" & DateTimePicker9.Value.ToString("HH:mm")
            WorkingHourString &= "|" & DateTimePicker10.Value.ToString("HH:mm")
            WorkingHourString &= "|" & DateTimePicker11.Value.ToString("HH:mm")
            NeedToUpdateWorkHour = True

            For i As Integer = 0 To nShops - 1
                'If isOnline(i) Then
                pool.Add(New message2Send(WorkingHourString, Shops(i), ShopsIP(i), ShopsPort(i), i))
                'UpdateWorkingHour(WorkingHourString, ShopsIP(i), ShopsPort(i))
                'End If
            Next
            Timer_sendMsg_Tick(Timer_sendMsg, e)
        End If
    End Sub

    Private Sub btn_cancel_Click(sender As Object, e As EventArgs) Handles btn_cancel.Click
        LoadWorkingHour()
    End Sub

#Region "Schedule_tab"

    '##############################################################
    '########### THIS IS FOR SCHEDULE TAB##########################
    '##############################################################

    Private Sub init_schedule()
        'Init
        If Not xlApp Is Nothing Then
            CloseExcel()
        End If

        listSheet.Items.Clear()
        ListShop.Items.Clear()
        ListShop.Items.Add("Body Shop (BDY)")
        ListShop.Items.Add("Paint Shop (PNT)")
        ListShop.Items.Add("Trim Shop (TRI)")
        ListShop.Items.Add("Chassis Shop (CHA)")
        ListShop.Items.Add("Final Shop (FNG)")
        ListShop.SelectedIndex = 0
        'ListShop.Enabled = False

        btn_select.Enabled = False
        btn_download.Enabled = False
        'btn_saveDB.Enabled = False
        'btn_readSche.Enabled = False

        DaysInMonth = 0

        Dim i, j As Integer
        For i = 0 To 5
            sname(i) = ""
            For j = 0 To 31
                sched(i, j) = ""
            Next
        Next

        ScheCalendar1.MinDate = New Date(Today.Year, Today.Month, 1)
        ScheCalendar1.MaxDate = New Date(Today.Year, Today.Month, Date.DaysInMonth(Today.Year, Today.Month))
        ScheCalendar1.SelectionRange.Start = ScheCalendar1.MinDate
        ScheCalendar1.SelectionRange.End = ScheCalendar1.MinDate

        ScheCalendar2.MinDate = ScheCalendar1.MinDate
        ScheCalendar2.MaxDate = ScheCalendar1.MaxDate
        ScheCalendar2.SelectionRange.Start = ScheCalendar2.MaxDate
        ScheCalendar2.SelectionRange.End = ScheCalendar2.MaxDate

        lbl_from.Text = ScheCalendar1.SelectionRange.Start
        lbl_to.Text = ScheCalendar2.SelectionRange.End

        ProgressBar1.Value = 0
        lbl_row.Visible = (DebugMode = 1)
        lbl_hidden.Visible = ShowHiddenSheets
    End Sub

    Private Sub GetListModels()
        models = New BindingSource
        Using connection As New SqlConnection(ConnectionString)
            connection.Open()
            Dim sql = "SELECT DISTINCT ModelID FROM Cycle"

            Dim cmd As New SqlCommand(sql, connection)
            cmd.CommandType = CommandType.Text

            Dim reader As SqlDataReader = cmd.ExecuteReader()
            While reader.Read
                models.Add(reader.GetString(0))
            End While
            reader.Close()
            cmd.Dispose()
        End Using
    End Sub

    Private Sub LoadDataFromSheet(s As Integer)
        ProgressBar1.Visible = True
        ProgressBar1.Value = 0
        lbl_progress.Visible = True

        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Open(ExcelFileName, , True)
        xlWorkSheet = xlWorkBook.Worksheets(s)

        'load general infor first
        Dim month As String = xlWorkSheet.Cells(1, 9).value
        If (month Is Nothing) OrElse (month.Trim = "") Then
            MessageBox.Show("Cell I1 is not a month")
            Exit Sub
        End If

        Dim day As Integer = xlWorkSheet.Cells(2, 9).value

        thisMonth = ConvertMonth2Int(month)

        Dim year As String = xlWorkSheet.Cells(1, 1).value
        Dim p As Integer = year.IndexOf("201")
        If p >= 0 Then
            thisYear = year.Substring(p, 4)
        Else
            thisYear = Today.Year
        End If
        DaysInMonth = Date.DaysInMonth(thisYear, thisMonth)

        lbl_month.Text = thisMonth & "/" & thisYear

        'load the data grid view
        With data1
            .RowCount = DaysInMonth
            For i = 0 To DaysInMonth - 1
                .Rows(i).Cells(0).Value = (New Date(thisYear, thisMonth, i + 1)).ToShortDateString
            Next
        End With

        'load the calendar
        ScheCalendar1.MinDate = New Date(thisYear, thisMonth, 1)
        ScheCalendar1.MaxDate = New Date(thisYear, thisMonth, Date.DaysInMonth(thisYear, thisMonth))
        ScheCalendar1.SelectionRange.Start = ScheCalendar1.MinDate
        ScheCalendar1.SelectionRange.End = ScheCalendar1.MinDate

        ScheCalendar2.MinDate = ScheCalendar1.MinDate
        ScheCalendar2.MaxDate = ScheCalendar1.MaxDate
        ScheCalendar2.SelectionRange.Start = ScheCalendar2.MaxDate
        ScheCalendar2.SelectionRange.End = ScheCalendar2.MaxDate

        lbl_from.Text = ScheCalendar1.SelectionRange.Start
        lbl_to.Text = ScheCalendar2.SelectionRange.End


        Dim shopName As String = "" 'NOTE: row first, col later
        Dim firstRow As Integer = 4
        Dim lastRow As Integer
        Dim col As Integer = 9 'starting from col I where date = 1
        Dim val As String
        Dim n As Integer = 0
        Dim objRange As Excel.Range

        While (True)
            val = xlWorkSheet.Cells(firstRow, 4).text
            If (val.Trim = "") OrElse (Not IsNumeric(val)) Then
                firstRow += 1
            Else
                Exit While
            End If
        End While

        For n = 0 To 4 'must be always has 5 shops
            objRange = xlWorkSheet.Range("A" & firstRow.ToString)
            'shopName = xlWorkSheet.Cells(firstRow, 1).value
            shopName = objRange.MergeArea(1, 1).text
            sname(n) = shopName.Trim.Replace(" ", "").ToUpper

            '--------20141210 - Add ---------
            If sname(0) = "BODY" Then sname(0) = "BDY"
            If sname(1) = "PAINT" Then sname(1) = "PNT"
            If sname(2) = "TRIM" Then sname(2) = "TRI"
            If sname(3) = "CHASSIS" Then sname(3) = "CHA"
            If (sname(4) = "FINAL") OrElse (sname(4) = "FINISH") Then sname(4) = "FNG"
            '--------20141210 - End ---------



            If (sname(0) <> "BDY") Then
                MessageBox.Show("Cell A4 is not BODY (BDY)")
                ProgressBar1.Visible = False
                lbl_progress.Visible = False
                Exit Sub
            End If
            lbl_progress.Text = "Loading schedule from Excel: " & shopName & "..."

            lastRow = firstRow

            'look at col D, until empty then lastRow
            While (True)

                val = xlWorkSheet.Cells(lastRow, 4).text
                If val.Trim = "" Then
                    Exit While
                Else
                    lastRow += 1
                End If
                'Console.WriteLine("Cells " & lastRow & ", 4: " & val)
                'MessageBox.Show("Cells " & lastRow & ", 4: " & val)
            End While
            lastRow -= 1

            'MessageBox.Show(shopName & " " & firstRow & " " & lastRow)
            lbl_row.Text = shopName & " " & firstRow & " " & lastRow

            Dim r, c As Integer
            Dim m, q, t, ct, wt As String 'model, qty, procTime, changeTime
            Dim plan As String = ""
            Dim pt As String = ""

            For c = col To col + DaysInMonth - 1
                plan = ""
                pt = ""
                For r = lastRow To firstRow Step -1
                    q = xlWorkSheet.Cells(r, c).text 'quantity
                    If (q.Trim <> "") AndAlso (IsNumeric(q.Trim)) Then
                        m = xlWorkSheet.Cells(r, 2).text 'model at col B
                        If (m.Trim <> "") Then
                            ct = xlWorkSheet.Cells(r, 7).text 'change time at col G
                            wt = xlWorkSheet.Cells(r, 8).text 'wait time at col H, only for shop Final
                            If (ct.Trim = "") Then ct = "0"
                            If (wt.Trim = "") Then wt = "0"
                            t = xlWorkSheet.Cells(r, 4).text 'time to process a unit, at col D

                            plan &= " " & (m.Trim & "(" & q.Trim & ")") & " " & "[" & ct.Trim & "]"
                            pt &= " " & ct.Trim & ":" & CStr(CInt(t.Trim) + CInt(wt.Trim))
                        End If
                    End If
                Next

                'remove last [time to change]
                If plan.EndsWith("]") Then
                    plan = plan.Remove(plan.LastIndexOf("["))
                End If
                If plan.Contains("SHUTDOWN") Then
                    plan = ""
                End If

                sched(n, c - col + 1) = plan.Trim
                ptime(n, c - col + 1) = pt.Trim

                ProgressBar1.Value = 20 * n + Convert.ToInt32((c + 1) * 20 / (lastRow - firstRow + 1))
                Application.DoEvents()
            Next

            'read the Work Time remain
            lastRow += 1
            While xlWorkSheet.Cells(lastRow, 2).text.ToString.Trim.ToUpper <> "WORK TIME REMAIN"
                lastRow += 1
            End While

            For c = 1 To DaysInMonth
                t = xlWorkSheet.Cells(lastRow, col + c - 1).text
                If t.Trim = "" Then t = 0
                WorkTimeRemain(n, c) = t
            Next

            'update first row to next shop
            If n < 4 Then
                firstRow = lastRow + 1
                While (True)
                    val = xlWorkSheet.Cells(firstRow, 4).text
                    If (val.Trim = "") OrElse (Not IsNumeric(val)) Then
                        firstRow += 1
                    Else
                        Exit While
                    End If
                End While

            End If

            If DebugMode Then Exit For 'drop the loop for testing only
        Next

        ProgressBar1.Visible = False
        lbl_progress.Visible = False

        'ListShop.Enabled = True
        ReadFromExcel = True
        ListShop.SelectedIndex = 0
        ListShop_SelectedIndexChanged(ListShop, New EventArgs)

        CloseExcel()

        btn_download.Enabled = True
    End Sub

    Private Sub OpenExcelFile()
        'open to get the list of sheets, then close
        Dim i As Integer

        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Open(ExcelFileName, , True)
        nSheets = xlWorkBook.Worksheets.Count

        For i = 1 To nSheets
            If DirectCast(xlWorkBook.Worksheets(i), Excel.Worksheet).Visible Then
                listSheet.Items.Add(xlWorkBook.Worksheets(i).Name)
            Else
                If ShowHiddenSheets Then
                    listSheet.Items.Add(xlWorkBook.Worksheets(i).Name & " (*)")
                End If
            End If
        Next

        nSheets = listSheet.Items.Count

        lbl_nSheets.Text = "This file contains " & nSheets & " sheet(s)."
        lbl_file.Text = ExcelFileName.Remove(0, ExcelFileName.LastIndexOf("\") + 1)

        lbl_plsSelect.Visible = (nSheets > 1)
        btn_select.Enabled = (nSheets > 1)


        'close it first, the excel will be open again in the LoadDataFromSheet
        CloseExcel()

        If nSheets = 1 Then
            selectedSheet = 1
            listSheet.SelectedIndex = 0
            LoadDataFromSheet(selectedSheet)
        End If


    End Sub

    Private Sub CloseExcel()
        Try
            'clean up and quit
            If Not xlWorkSheet Is Nothing Then
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing
            End If

            If Not xlWorkBook Is Nothing Then
                xlWorkBook.Close(False) 'close, no save
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkBook)
                xlWorkBook = Nothing
            End If
            
            If Not xlApp Is Nothing Then
                xlApp.Workbooks.Close()
                xlApp.Quit()
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)
                xlApp = Nothing
            End If
            
        Catch ex As Exception

        End Try
        
    End Sub

    Private Sub btn_load_Click(sender As Object, e As EventArgs) Handles btn_load.Click
        With OpenFileDialog1
            .Title = "Open schedule file"
            .Filter = "Excel Files (*.xlsx)|*.xlsx"

            If .ShowDialog = Windows.Forms.DialogResult.OK Then
                init_schedule()

                Cursor = Cursors.WaitCursor

                ExcelFileName = .FileName
                OpenExcelFile()

                Cursor = Cursors.Default

                'MonthCalendar1.MinDate = New Date(thisYear, thisMonth, 1)
                'MonthCalendar1.MaxDate = New Date(thisYear, thisMonth, Date.DaysInMonth(thisYear, thisMonth))

            End If
        End With
    End Sub

    Private Sub btn_select_Click(sender As Object, e As EventArgs) Handles btn_select.Click
        selectedSheet = listSheet.SelectedIndex + 1
        LoadDataFromSheet(selectedSheet)
    End Sub

    Private Sub ListShop_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ListShop.SelectedIndexChanged
        Dim i As Integer = ListShop.SelectedIndex
        If i < 0 Then Exit Sub

        Dim r As Integer
        Dim shopName As String = ""
        Dim st As String = ""

        Select Case i
            Case 0 : shopName = "BDY"
            Case 1 : shopName = "PNT"
            Case 2 : shopName = "TRI"
            Case 3 : shopName = "CHA"
            Case 4 : shopName = "FNG"
            Case Else : shopName = ""
        End Select

        lbl_schedule.Text = "Schedule for " & shopName
        btn_download.Text = "Download to " & shopName & " shop"
        btn_readSche.Text = "Read schedule from " & shopName & " shop"

        With data1
            For r = 0 To DaysInMonth - 1
                If ReadFromExcel Then
                    If (sched(i, r + 1) = "") Then
                        .Rows(r).Cells(1).Value = "No plan"
                        .Rows(r).Cells(0).Style.BackColor = Color.LightGreen
                        .Rows(r).Cells(1).Style.BackColor = Color.LightGreen
                        .Rows(r).Cells(2).Style.BackColor = Color.LightGreen
                        .Rows(r).Cells(4).Style.BackColor = Color.LightGreen
                    Else
                        .Rows(r).Cells(1).Value = sched(i, r + 1)
                        .Rows(r).Cells(3).Value = ptime(i, r + 1)

                        .Rows(r).Cells(0).Style.BackColor = Color.White
                        .Rows(r).Cells(1).Style.BackColor = Color.White
                        .Rows(r).Cells(2).Style.BackColor = Color.White
                        .Rows(r).Cells(4).Style.BackColor = Color.White
                    End If
                    .Rows(r).Cells(4).Value = WorkTimeRemain(i, r + 1) & " | ?"

                Else
                    .Rows(r).Cells(4).Value = ""
                End If
                
                .Rows(r).Cells(2).Value = "" 'clear the "schedule from tool" when change shop

            Next
        End With

        'btn_download.Enabled = (data1.RowCount > 0)
        '.Enabled = True
        'btn_readSche.Enabled = True
    End Sub

    Private Sub btn_saveDB_Click(sender As Object, e As EventArgs) Handles btn_saveDB.Click
        Save2DB()
        MessageBox.Show("Saved schedule for " & sname(ListShop.SelectedIndex) & " shop in " & thisMonth & "/" & thisYear)
    End Sub

    Private Sub Save2DB()
        Dim m As String = thisYear & thisMonth.ToString("00")
        Dim s As String = ""
        Dim plan As String = ""
        Dim thisListContainsToday As Boolean = False

        Dim pArr(), tArr() As String

        Dim i, j As Integer

        i = ListShop.SelectedIndex
        Select Case i
            Case 0 : s = "BDY"
            Case 1 : s = "PNT"
            Case 2 : s = "TRI"
            Case 3 : s = "CHA"
            Case 4 : s = "FNG"
            Case Else : s = ""
        End Select

        Using connection As New SqlConnection(ConnectionString)
            connection.Open()

            'delete all in the db with the same month
            Dim sql = String.Format("DELETE FROM Schedule WHERE StepId='{0}' AND MO='{1}'", _
                                    s, m)

            Dim cmd As New SqlCommand(sql, connection)
            cmd.CommandType = CommandType.Text
            cmd.ExecuteNonQuery()

            'now insert
            With data1
                For i = 0 To .RowCount - 1
                    If .Rows(i).Cells(1).Value <> "No plan" Then
                        If .Rows(i).Cells(0).Value = Today.Date Then
                            thisListContainsToday = True
                        End If

                        'plan = .Rows(i).Cells(1).Value.ToString.Trim
                        'plan = plan.Replace("(", ":")
                        'plan = plan.Replace(")", "|")
                        'plan = plan.Remove(plan.Length - 1) 'remove last char

                        pArr = .Rows(i).Cells(1).Value.ToString.Trim.Split(" ")
                        tArr = .Rows(i).Cells(3).Value.ToString.Trim.Split(" ")

                        plan = ""
                        For j = 0 To pArr.Length - 1
                            If (j Mod 2 = 0) Then
                                plan &= pArr(j).Replace("(", ":").Replace(")", ":") & tArr(j \ 2) & "|"
                            End If
                        Next
                        plan = plan.Remove(plan.Length - 1) 'remove last char "|"

                        sql = String.Format("INSERT INTO Schedule(DateId, StepId, DetailsPlan, MO) VALUES ('{0}', '{1}', '{2}', '{3}')", _
                                            CDate(.Rows(i).Cells(0).Value).ToString("yyyy-MM-dd"), s, plan, m)
                        cmd = New SqlCommand(sql, connection)
                        cmd.ExecuteNonQuery()
                    End If
                Next
            End With

            cmd.Dispose()
            'MessageBox.Show("Successfully save schedule for " & thisMonth.ToString("00") & "/" & thisYear & " for " & s & " to database")
        End Using


        If thisListContainsToday Then
            LoadTarget()
        End If
    End Sub

    Private Sub btn_download_Click(sender As Object, e As EventArgs) Handles btn_download.Click
        Dim i, j As Integer
        Dim fW As StreamWriter
        Dim fn As String = ""
        Dim m, q As String
        Dim stArr(), tArr() As String
        Dim ip As String = ShopsIP(ListShop.SelectedIndex).Trim

        Dim st As Integer = ScheCalendar1.SelectionStart.Day - 1
        Dim en As Integer = ScheCalendar2.SelectionStart.Day - 1

        Dim msg As String = "SCHE|" & Today.ToString("MMddyyyy")
        Dim line As String = ""

        'ProgressBar1.Visible = True
        'ProgressBar1.Value = 0
        'lbl_progress.Visible = True
        'lbl_progress.Text = "Downloading schedule for " & sname(ListShop.SelectedIndex) & " shop..."

        'copy file to shops
        With data1
            'first save WorkTimeRemain fo the whole month
            Try
                Dim tt As String
                If DebugMode Then
                    fW = New StreamWriter("remain.txt")
                Else
                    fW = New StreamWriter("\\" & ip & "\isuzu\remain.txt")
                End If

                For i = 0 To .RowCount - 1
                    tt = .Rows(i).Cells(4).Value.ToString.Trim
                    If tt.Contains(" ") Then
                        tt = tt.Remove(tt.IndexOf(" "))
                    End If
                    If tt = "" Then tt = "0"
                    fW.WriteLine(tt)
                Next
                fW.Close()
            Catch ex As Exception
                'if DebugMode Then
                MessageBox.Show(ex.ToString, "Failed to save Work time remain")
                'End If
            End Try

            For i = st To en
                If .Rows(i).Cells(1).Value <> "No plan" Then
                    Try
                        If DebugMode Then
                            fW = New StreamWriter("schedule_" & _
                                              thisYear & thisMonth.ToString("00") & (i + 1).ToString("00") & ".txt")
                        Else
                            fW = New StreamWriter("\\" & ip & "\isuzu\schedule\schedule_" & _
                                              thisYear & thisMonth.ToString("00") & (i + 1).ToString("00") & ".txt")
                        End If
                        
                        fW.WriteLine(thisMonth.ToString("00") & "/" & (i + 1).ToString("00") & "/" & thisYear)

                        stArr = .Rows(i).Cells(1).Value.ToString.Trim.Split(" ")
                        tArr = .Rows(i).Cells(3).Value.ToString.Trim.Split(" ")

                        For j = 0 To stArr.Length - 1
                            'm = stArr(j).Remove(stArr(j).IndexOf("("))
                            'q = stArr(j).Remove(0, stArr(j).IndexOf("(") + 1)
                            'q = q.Remove(q.IndexOf(")"))
                            If (j Mod 2 = 0) Then
                                line = stArr(j).Replace("(", "|").Replace(")", "|") & _
                                                                         tArr(j \ 2).Replace(":", "|")
                                fW.WriteLine(line)

                                If .Rows(i).Cells(0).Value = Today.Date Then
                                    msg &= ("|" & line)
                                End If
                            End If
                        Next

                        fW.Close()

                        '.Rows(i).Cells(2).Value = .Rows(i).Cells(1).Value 'dont display this line
                    Catch ex As Exception
                        'If DebugMode Then
                        MessageBox.Show(ex.ToString, "Failed to save schedule for " & thisMonth.ToString("00") & "/" & (i + 1).ToString("00"))
                        'End If
                    End Try
                Else
                    'no plan then delete the file on client
                    Try
                        fn = "\\" & ip & "\isuzu\schedule\schedule_" & _
                                              thisYear & thisMonth.ToString("00") & (i + 1).ToString("00") & ".txt"

                        If File.Exists(fn) Then
                            File.Delete(fn)
                        End If

                    Catch ex As Exception
                        'If DebugMode Then
                        MessageBox.Show(ex.ToString, "Failed to delete file")
                        'End If
                    End Try
                End If
            Next
        End With

        'ProgressBar1.Visible = False
        'lbl_progress.Visible = False

        'if this is selected to be downloaded to shop, then it's ok to save to db as well
        Save2DB()

        'send the schedule of today via TCP
        'SCHE|MMddyyyy|Model1|qty|changeTime|duration|Model2|...
        If msg.Length > 15 Then
            i = ListShop.SelectedIndex
            'If isOnline(i) Then
            pool.Add(New message2Send(msg, Shops(i), ShopsIP(i), ShopsPort(i), i))
            'End If
        End If

        MessageBox.Show("Finish downloading schedule for " & sname(ListShop.SelectedIndex) & " shop" & _
                        vbCrLf & "from " & lbl_from.Text & " to " & lbl_to.Text)
    End Sub

    Private Sub btn_readSche_Click(sender As Object, e As EventArgs) Handles btn_readSche.Click
        Dim i, j As Integer
        Dim fR As StreamReader
        Dim st, m, q, ct As String
        Dim stArr() As String
        Dim plan As String = ""
        Dim fn As String
        Dim ip As String = ShopsIP(ListShop.SelectedIndex)

        Dim sta As Integer = ScheCalendar1.SelectionStart.Day - 1
        Dim en As Integer = ScheCalendar2.SelectionStart.Day - 1

        'ProgressBar1.Visible = True
        'ProgressBar1.Value = 0
        'lbl_progress.Visible = True
        'lbl_progress.Text = "Downloading schedule for " & sname(ListShop.SelectedIndex) & " shop..."

        If data1.RowCount = 0 Then
            thisMonth = Today.Month
            thisYear = Today.Year
            DaysInMonth = Date.DaysInMonth(thisYear, thisMonth)

            'load the data grid view
            With data1
                .RowCount = DaysInMonth
                For i = 0 To DaysInMonth - 1
                    .Rows(i).Cells(0).Value = (New Date(thisYear, thisMonth, i + 1)).ToShortDateString
                Next
            End With
        End If


        'copy file to shops
        With data1
            '1st read the remain.txt
            fn = "\\" & ip & "\isuzu\remain.txt"
            'fn = "remain.txt"
            If File.Exists(fn) Then
                fR = New StreamReader(fn)
                'day = 4 then sta=3 -> skip first 3 numbers
                For i = 1 To sta
                    fR.ReadLine()
                Next

                For i = sta To en
                    If (.Rows(i).Cells(4).Value Is Nothing) OrElse (.Rows(i).Cells(4).Value = "") Then
                        .Rows(i).Cells(4).Value = "? | " & fR.ReadLine
                    Else
                        st = .Rows(i).Cells(4).Value.ToString.Trim
                        If st.Contains(" ") Then
                            st = st.Remove(st.IndexOf(" "))
                        End If
                        .Rows(i).Cells(4).Value = st & " | " & fR.ReadLine
                    End If

                Next
                fR.Close()

            Else 'file not exist, set 0 for all
                For i = sta To en
                    .Rows(i).Cells(4).Value &= "? | 0"
                Next

            End If
            

            For i = sta To en
                plan = ""
                m = ""
                q = ""
                ct = ""
                Try
                    fn = "\\" & ip & "\isuzu\schedule\schedule_" & _
                                          thisYear & thisMonth.ToString("00") & (i + 1).ToString("00") & ".txt"
                    'fn = "20141223.txt"
                    If File.Exists(fn) Then
                        fR = New StreamReader(fn)
                        fR.ReadLine() 'skip this 1st line

                        While fR.Peek <> -1
                            st = fR.ReadLine
                            stArr = st.Split("|")
                            m = stArr(0)
                            q = stArr(1)
                            ct = stArr(2)
                            plan &= " " & m & "(" & q & ")" & " [" & ct & "]"
                        End While

                        fR.Close()

                        plan = plan.Remove(plan.LastIndexOf("["))
                        .Rows(i).Cells(2).Value = plan.Trim

                    Else
                        .Rows(i).Cells(2).Value = "No plan"

                    End If

                    If Not (.Rows(i).Cells(1).Value Is Nothing) Then
                        If .Rows(i).Cells(1).Value.ToString.Trim <> .Rows(i).Cells(2).Value.ToString.Trim Then
                            .Rows(i).Cells(2).Style.BackColor = Color.Coral
                        End If
                    End If

                Catch ex As Exception
                    If DebugMode Then
                        MessageBox.Show(ex.ToString)
                    End If
                End Try

            Next
        End With

        'ProgressBar1.Visible = False
        'lbl_progress.Visible = False

    End Sub

    Private Sub MonthCalendar1_DateChanged(sender As Object, e As DateRangeEventArgs) Handles ScheCalendar1.DateChanged
        If ScheCalendar2.SelectionRange.Start < ScheCalendar1.SelectionRange.End Then
            'MonthCalendar2.SelectionRange.Start = MonthCalendar1.SelectionRange.End
            'MonthCalendar2.SelectionRange.End = MonthCalendar1.SelectionRange.End
            ScheCalendar2.SelectionRange = ScheCalendar1.SelectionRange
        End If

        lbl_from.Text = ScheCalendar1.SelectionRange.Start
        lbl_to.Text = ScheCalendar2.SelectionRange.End
    End Sub

    Private Sub MonthCalendar2_DateChanged(sender As Object, e As DateRangeEventArgs) Handles ScheCalendar2.DateChanged
        If ScheCalendar1.SelectionRange.Start > ScheCalendar2.SelectionRange.End Then
            'MonthCalendar1.SelectionRange.Start = MonthCalendar2.SelectionRange.End
            'MonthCalendar1.SelectionRange.End = MonthCalendar2.SelectionRange.End
            ScheCalendar1.SelectionRange = ScheCalendar2.SelectionRange
        End If

        lbl_from.Text = ScheCalendar1.SelectionRange.Start
        lbl_to.Text = ScheCalendar2.SelectionRange.End
    End Sub

#End Region


#Region "Stat_tab"
    Private Sub init_stat()
        lstShopStat.Items.Clear()
        lstShopStat.Items.Add("Body Shop (BDY)")
        lstShopStat.Items.Add("Paint Shop (PNT)")
        lstShopStat.Items.Add("Trim Shop (TRI)")
        lstShopStat.Items.Add("Chassis Shop (CHA)")
        lstShopStat.Items.Add("Final Shop (FNG)")
        lstShopStat.SelectedIndex = 0

        data_stat.RowCount = 0
        data_stat_detail.RowCount = 0
    End Sub

    Private Sub btn_stat_Click_1(sender As Object, e As EventArgs) Handles btn_stat.Click
        Dim i As Integer = lstShopStat.SelectedIndex
        Dim shopCode As String = Shops(i)

        data_stat.RowCount = 0
        data_stat_detail.RowCount = 0

        Using connection As New SqlConnection(ConnectionString)
            connection.Open()

            Dim sql = String.Format("SELECT EndDate, StepId, TruckId, count(Duration) as [N], avg(Duration) as [Ave] " & _
                                    "FROM Production WHERE StepId='{0}' AND (EndDate>='{1}' AND EndDate<='{2}') " & _
                                    "GROUP BY EndDate, StepId, TruckId " & _
                                    "ORDER BY EndDate ASC", _
                                    shopCode, _
                                    StatCalendar1.SelectionStart.ToString("yyyy-MM-dd"), _
                                    StatCalendar2.SelectionStart.ToString("yyyy-MM-dd"))
            

            Dim cmd As New SqlCommand(sql, connection)
            cmd.CommandType = CommandType.Text
            Dim a As SqlDataReader
            a = cmd.ExecuteReader()

            While a.Read
                With data_stat
                    .RowCount += 1
                    .Rows(.RowCount - 1).Cells(0).Value = a.GetString(0)
                    .Rows(.RowCount - 1).Cells(1).Value = a.GetString(2)
                    .Rows(.RowCount - 1).Cells(2).Value = a.GetInt32(3)
                    .Rows(.RowCount - 1).Cells(3).Value = CSng(a.GetValue(4)).ToString("0.0")

                End With
                
            End While

            a.Close()
            cmd.Dispose()
        End Using


        'detail
        Using connection As New SqlConnection(ConnectionString)
            connection.Open()

            Dim sql = String.Format("SELECT EndDate, StepId, TruckId, EndTime, Duration " & _
                                    "FROM Production WHERE StepId='{0}' AND (EndDate>='{1}' AND EndDate<='{2}') " & _
                                    "ORDER BY EndDate ASC, EndTime ASC", _
                                    shopCode, _
                                    StatCalendar1.SelectionStart.ToString("yyyy-MM-dd"), _
                                    StatCalendar2.SelectionStart.ToString("yyyy-MM-dd"))


            Dim cmd As New SqlCommand(sql, connection)
            cmd.CommandType = CommandType.Text
            Dim a As SqlDataReader
            a = cmd.ExecuteReader()

            Dim stt As Integer = 0
            Dim dd As String = ""
            Dim pre_d As String = ""

            While a.Read
                With data_stat_detail
                    .RowCount += 1

                    dd = a.GetString(0)
                    If (dd <> pre_d) Then
                        stt = 1
                        pre_d = dd
                        .Rows(.RowCount - 1).Cells(0).Value = dd
                    Else
                        stt += 1
                        .Rows(.RowCount - 1).Cells(0).Value = ""
                    End If

                    .Rows(.RowCount - 1).Cells(1).Value = stt
                    .Rows(.RowCount - 1).Cells(2).Value = a.GetString(2)
                    .Rows(.RowCount - 1).Cells(3).Value = a.GetString(3)
                    .Rows(.RowCount - 1).Cells(4).Value = CSng(a.GetValue(4)).ToString("0.0")

                End With

            End While

            a.Close()
            cmd.Dispose()
        End Using
    End Sub
#End Region

End Class
